package com.qitmiaojie.mapper;

import com.qitmiaojie.pojo.Health;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qitmiaojie.pojo.dto.HealthCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @Entity com.qitmiaojie.pojo.Health
 */
@Mapper
public interface HealthMapper extends BaseMapper<Health> {



    /***
    * @description
     * 查询诺如 新冠 甲流 分别确诊人数、疑似人数
     * 使用UNION ALL 合并查询
     * 合并为一个SQL查询语句，虽然需要一次性返回所有结果，但可以减少与数据库的交互次数，降低网络传输和数据库开销，
     * 从而在一定程度上提高了查询效率。
     * 此外，合并查询还可以使用数据库的查询优化功能，对查询语句进行优化，进一步提高查询效率。
     * 因此，要选择单独分开查询还是一个 SQL 查询，
     * 需要综合考虑数据量、查询频率、网络传输、系统负载等多种因素，并根据具体情况进行评估。
     * 同时，可以根据实际情况采用索引、缓存等技术手段来进一步优化查询效率。
    * @param startDate
     * @param endDate
    * @return List<HealthCount>
    * @author miaojie
    * @date 2023/3/12 11:41
    */
    @Select("<script>" +
            "SELECT 'jialiu' AS type, h.jialiu AS symptom, COUNT(*) AS count FROM health h WHERE h.jialiu IN ('是', '疑似')" +
            "<if test='startDate != null'>" +
            " AND h.date &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null'>" +
            " AND h.date &lt;= #{endDate}" +
            "</if>" +
            " GROUP BY h.jialiu " +
            "UNION ALL " +
            "SELECT 'xinguan' AS type, h.xinguan AS symptom, COUNT(*) AS count FROM health h WHERE h.xinguan IN ('是', '疑似')" +
            "<if test='startDate != null'>" +
            " AND h.date &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null'>" +
            " AND h.date &lt;= #{endDate}" +
            "</if>" +
            " GROUP BY h.xinguan " +
            "UNION ALL " +
            "SELECT 'nuoru' AS type, h.nuoru AS symptom, COUNT(*) AS count FROM health h WHERE h.nuoru IN ('是', '疑似')" +
            "<if test='startDate != null'>" +
            " AND h.date &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null'>" +
            " AND h.date &lt;= #{endDate}" +
            "</if>" +
            " GROUP BY h.nuoru" +
            "</script>")
    List<HealthCount> selectSymptomCounts(@Param("startDate") String startDate, @Param("endDate") String endDate);


    /***
    * @description 查询每个月 确诊的人数
    * @param type  查询是甲流还是诺如还是新冠
    * @return
    * @author
    * @date
    */
    @Select("SELECT DATE_FORMAT(DATE,'%m') AS MONTH, COUNT(*) AS COUNT " +
            "FROM health " +
            "WHERE ${type} = '是' " +
            "GROUP BY MONTH " +
            "ORDER BY MONTH ASC")
    List<Map<String, Object>> selectConfirmedCount(@Param("type") String type
                                                  );
}




